﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TaxBusiness.Model
{
    public class CountCondition
    {
        private string _count1 = "select tb_office.Name as UnitName" +
                        ",isnull(tb.tp2,0) as tp2" +
                        ",isnull(tb.ctp2,0) as ctp2" +
                        ",isnull(tb.tp3,0) as tp3" +
                        ",isnull(tb.ctp3,0)as ctp3" +
                        ",isnull(tb.tp4,0) as tp4" +
                        ",isnull(tb.ctp4,0)as ctp4" +
                        ",isnull(tb.tp5,0) as tp5" +
                        ",isnull(tb.ctp5,0)as ctp5" +
                        ",isnull(tb.tp6,0) as tp6" +
                        ",isnull(tb.ctp6,0) as ctp6" +
                        ",isnull(tb.tp7,0) as tp7" +
                        ",isnull(tb.ctp7,0) as ctp7" +
                        ",isnull(tb.tp8,0) as tp8" +
                        ",isnull(tb.ctp8,0) as ctp8" +
                        ",isnull(tb.tp9,0) as tp9" +
                        ",isnull(tb.ctp9,0) as ctp9" +
                        ",isnull(tb.tp10,0) as tp10" +
                        ",isnull(tb.ctp10,0) as ctp10" +
                        ",isnull(tb.tp11,0) as tp11" +
                        ",isnull(tb.ctp11,0) as ctp11" +
                        ",isnull(tb.tp12,0) as tp12" +
                        ",isnull(tb.ctp12,0) as ctp12" +
                        ",isnull(tb.tp13,0) as tp13" +
                        ",isnull(tb.ctp13,0) as ctp13" +
                        ",isnull(tb.tp14,0) as tp14" +
                        ",isnull(tb.ctp14,0) as ctp14" +
                        ",isnull(tb.tp15,0) as tp15" +
                        ",isnull(tb.ctp15,0) as ctp15" +
                        ",isnull(tb.tp16,0) as tp16" +
                        ",isnull(tb.ctp16,0) as ctp16" +
                        ",isnull(tb.tp17,0) as tp17" +
                        ",isnull(tb.ctp17,0) as ctp17" +
                        ",isnull(tb.tp18,0) as tp18" +
                        ",isnull(tb.ctp18,0) as ctp18" +
                        ",isnull(tb.tp19,0) as tp19" +
                        ",isnull(tb.ctp19,0) as ctp19" +
                        ",isnull(tb.tp20,0) as tp20" +
                        ",isnull(tb.ctp20,0) as ctp20" +
                        ",isnull(tb.tp21,0) as tp21" +
                        ",isnull(tb.ctp21,0) as ctp21" +
                        ",isnull(tb.tp22,0) as tp22" +
                        ",isnull(tb.ctp22,0) as ctp22" +
                        ",isnull(tb.tp23,0) as tp23" +
                        ",isnull(tb.ctp23,0) as ctp23 from tb_office left join" +
                        "(select theoffice" +
                        ",sum(case when businesstype=2 then chargingmoney else 0 end) as tp2" +
                        ",sum(case when businesstype=2 then 1 else 0 end) as ctp2" +
                        ",sum(case when businesstype=3 then chargingmoney else 0 end) as tp3" +
                        ",sum(case when businesstype=3 then 1 else 0 end) as ctp3" +
                        ",sum(case when businesstype=4 then chargingmoney else 0 end) as tp4" +
                        ",sum(case when businesstype=4 then 1 else 0 end) as ctp4" +
                        ",sum(case when businesstype=5 then chargingmoney else 0 end) as tp5" +
                        ",sum(case when businesstype=5 then 1 else 0 end) as ctp5" +
                        ",sum(case when businesstype=6 then chargingmoney else 0 end) as tp6" +
                        ",sum(case when businesstype=6 then 1 else 0 end) as ctp6" +
                        ",sum(case when businesstype=7 then chargingmoney else 0 end) as tp7" +
                        ",sum(case when businesstype=7 then 1 else 0 end) as ctp7" +
                        ",sum(case when businesstype=8 then chargingmoney else 0 end) as tp8" +
                        ",sum(case when businesstype=8 then 1 else 0 end) as ctp8" +
                        ",sum(case when businesstype=9 then chargingmoney else 0 end) as tp9" +
                        ",sum(case when businesstype=9 then 1 else 0 end) as ctp9" +
                        ",sum(case when businesstype=10 then chargingmoney else 0 end) as tp10" +
                        ",sum(case when businesstype=10 then 1 else 0 end) as ctp10" +
                        ",sum(case when businesstype=11 then chargingmoney else 0 end) as tp11" +
                        ",sum(case when businesstype=11 then 1 else 0 end) as ctp11" +
                        ",sum(case when businesstype=12 then chargingmoney else 0 end) as tp12" +
                        ",sum(case when businesstype=12 then 1 else 0 end) as ctp12" +
                        ",sum(case when businesstype=13 then chargingmoney else 0 end) as tp13" +
                        ",sum(case when businesstype=13 then 1 else 0 end) as ctp13" +
                        ",sum(case when businesstype=14 then chargingmoney else 0 end) as tp14" +
                        ",sum(case when businesstype=14 then 1 else 0 end) as ctp14" +
                        ",sum(case when businesstype=15 then chargingmoney else 0 end) as tp15" +
                        ",sum(case when businesstype=15 then 1 else 0 end) as ctp15" +
                        ",sum(case when businesstype=16 then chargingmoney else 0 end) as tp16" +
                        ",sum(case when businesstype=16 then 1 else 0 end) as ctp16" +
                        ",sum(case when businesstype=17 then chargingmoney else 0 end) as tp17" +
                        ",sum(case when businesstype=17 then 1 else 0 end) as ctp17" +
                        ",sum(case when businesstype=18 then chargingmoney else 0 end) as tp18" +
                        ",sum(case when businesstype=18 then 1 else 0 end) as ctp18" +
                        ",sum(case when businesstype=19 then chargingmoney else 0 end) as tp19" +
                        ",sum(case when businesstype=19 then 1 else 0 end) as ctp19" +
                        ",sum(case when businesstype=20 then chargingmoney else 0 end) as tp20" +
                        ",sum(case when businesstype=20 then 1 else 0 end) as ctp20" +
                        ",sum(case when businesstype=21 then chargingmoney else 0 end) as tp21" +
                        ",sum(case when businesstype=21 then 1 else 0 end) as ctp21" +
                        ",sum(case when businesstype=22 then chargingmoney else 0 end) as tp22" +
                        ",sum(case when businesstype=22 then 1 else 0 end) as ctp22" +
                        ",sum(case when businesstype=23 then chargingmoney else 0 end) as tp23" +
                        ",sum(case when businesstype=23 then 1 else 0 end) as ctp23" +
                        " from tb_Business where chargingDate>=@Date1 and chargingDate<=@Date2 group by theoffice) tb" +
                        " on tb_office.id = tb.theoffice";

        private string _count2 = "select tb_department.Name as UnitName" +
                        ",isnull(tb.tp2,0) as tp2" +
                        ",isnull(tb.ctp2,0) as ctp2" +
                        ",isnull(tb.tp3,0) as tp3" +
                        ",isnull(tb.ctp3,0)as ctp3" +
                        ",isnull(tb.tp4,0) as tp4" +
                        ",isnull(tb.ctp4,0)as ctp4" +
                        ",isnull(tb.tp5,0) as tp5" +
                        ",isnull(tb.ctp5,0)as ctp5" +
                        ",isnull(tb.tp6,0) as tp6" +
                        ",isnull(tb.ctp6,0) as ctp6" +
                        ",isnull(tb.tp7,0) as tp7" +
                        ",isnull(tb.ctp7,0) as ctp7" +
                        ",isnull(tb.tp8,0) as tp8" +
                        ",isnull(tb.ctp8,0) as ctp8" +
                        ",isnull(tb.tp9,0) as tp9" +
                        ",isnull(tb.ctp9,0) as ctp9" +
                        ",isnull(tb.tp10,0) as tp10" +
                        ",isnull(tb.ctp10,0) as ctp10" +
                        ",isnull(tb.tp11,0) as tp11" +
                        ",isnull(tb.ctp11,0) as ctp11" +
                        ",isnull(tb.tp12,0) as tp12" +
                        ",isnull(tb.ctp12,0) as ctp12" +
                        ",isnull(tb.tp13,0) as tp13" +
                        ",isnull(tb.ctp13,0) as ctp13" +
                        ",isnull(tb.tp14,0) as tp14" +
                        ",isnull(tb.ctp14,0) as ctp14" +
                        ",isnull(tb.tp15,0) as tp15" +
                        ",isnull(tb.ctp15,0) as ctp15" +
                        ",isnull(tb.tp16,0) as tp16" +
                        ",isnull(tb.ctp16,0) as ctp16" +
                        ",isnull(tb.tp17,0) as tp17" +
                        ",isnull(tb.ctp17,0) as ctp17" +
                        ",isnull(tb.tp18,0) as tp18" +
                        ",isnull(tb.ctp18,0) as ctp18" +
                        ",isnull(tb.tp19,0) as tp19" +
                        ",isnull(tb.ctp19,0) as ctp19" +
                        ",isnull(tb.tp20,0) as tp20" +
                        ",isnull(tb.ctp20,0) as ctp20" +
                        ",isnull(tb.tp21,0) as tp21" +
                        ",isnull(tb.ctp21,0) as ctp21" +
                        ",isnull(tb.tp22,0) as tp22" +
                        ",isnull(tb.ctp22,0) as ctp22" +
                        ",isnull(tb.tp23,0) as tp23" +
                        ",isnull(tb.ctp23,0) as ctp23 from tb_department left join" +
                        "(select thedepartment" +
                        ",sum(case when businesstype=2 then chargingmoney else 0 end) as tp2" +
                        ",sum(case when businesstype=2 then 1 else 0 end) as ctp2" +
                        ",sum(case when businesstype=3 then chargingmoney else 0 end) as tp3" +
                        ",sum(case when businesstype=3 then 1 else 0 end) as ctp3" +
                        ",sum(case when businesstype=4 then chargingmoney else 0 end) as tp4" +
                        ",sum(case when businesstype=4 then 1 else 0 end) as ctp4" +
                        ",sum(case when businesstype=5 then chargingmoney else 0 end) as tp5" +
                        ",sum(case when businesstype=5 then 1 else 0 end) as ctp5" +
                        ",sum(case when businesstype=6 then chargingmoney else 0 end) as tp6" +
                        ",sum(case when businesstype=6 then 1 else 0 end) as ctp6" +
                        ",sum(case when businesstype=7 then chargingmoney else 0 end) as tp7" +
                        ",sum(case when businesstype=7 then 1 else 0 end) as ctp7" +
                        ",sum(case when businesstype=8 then chargingmoney else 0 end) as tp8" +
                        ",sum(case when businesstype=8 then 1 else 0 end) as ctp8" +
                        ",sum(case when businesstype=9 then chargingmoney else 0 end) as tp9" +
                        ",sum(case when businesstype=9 then 1 else 0 end) as ctp9" +
                        ",sum(case when businesstype=10 then chargingmoney else 0 end) as tp10" +
                        ",sum(case when businesstype=10 then 1 else 0 end) as ctp10" +
                        ",sum(case when businesstype=11 then chargingmoney else 0 end) as tp11" +
                        ",sum(case when businesstype=11 then 1 else 0 end) as ctp11" +
                        ",sum(case when businesstype=12 then chargingmoney else 0 end) as tp12" +
                        ",sum(case when businesstype=12 then 1 else 0 end) as ctp12" +
                        ",sum(case when businesstype=13 then chargingmoney else 0 end) as tp13" +
                        ",sum(case when businesstype=13 then 1 else 0 end) as ctp13" +
                        ",sum(case when businesstype=14 then chargingmoney else 0 end) as tp14" +
                        ",sum(case when businesstype=14 then 1 else 0 end) as ctp14" +
                        ",sum(case when businesstype=15 then chargingmoney else 0 end) as tp15" +
                        ",sum(case when businesstype=15 then 1 else 0 end) as ctp15" +
                        ",sum(case when businesstype=16 then chargingmoney else 0 end) as tp16" +
                        ",sum(case when businesstype=16 then 1 else 0 end) as ctp16" +
                        ",sum(case when businesstype=17 then chargingmoney else 0 end) as tp17" +
                        ",sum(case when businesstype=17 then 1 else 0 end) as ctp17" +
                        ",sum(case when businesstype=18 then chargingmoney else 0 end) as tp18" +
                        ",sum(case when businesstype=18 then 1 else 0 end) as ctp18" +
                        ",sum(case when businesstype=19 then chargingmoney else 0 end) as tp19" +
                        ",sum(case when businesstype=19 then 1 else 0 end) as ctp19" +
                        ",sum(case when businesstype=20 then chargingmoney else 0 end) as tp20" +
                        ",sum(case when businesstype=20 then 1 else 0 end) as ctp20" +
                        ",sum(case when businesstype=21 then chargingmoney else 0 end) as tp21" +
                        ",sum(case when businesstype=21 then 1 else 0 end) as ctp21" +
                        ",sum(case when businesstype=22 then chargingmoney else 0 end) as tp22" +
                        ",sum(case when businesstype=22 then 1 else 0 end) as ctp22" +
                        ",sum(case when businesstype=23 then chargingmoney else 0 end) as tp23" +
                        ",sum(case when businesstype=23 then 1 else 0 end) as ctp23" +
                        " from tb_Business where chargingDate>=@Date1 and chargingDate<=@Date2 group by thedepartment) tb" +
                        " on tb_department.id = tb.thedepartment";

        public string CountByOffice
        {
            get { return _count1; }
        }

        public string CountByDepartment
        {
            get { return _count2; }
        }
    }
}
